﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class ProceduresDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoProcedures model)
        {
            model.PRO_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Procedures]");
            sql.Append("([PRO_ID],[PRO_CONTESTS],[PRO_PHASES],[PRO_STARTTIME],[PRO_ENDTIME],[PRO_ADDRESS],[PRO_CONDUCTOR],[PRO_CNRTITLE],[PRO_METHOD],[PRO_CONTENT])");
            sql.Append("VALUES");
            sql.Append("(@PRO_ID,@PRO_CONTESTS,@PRO_PHASES,@PRO_STARTTIME,@PRO_ENDTIME,@PRO_ADDRESS,@PRO_CONDUCTOR,@PRO_CNRTITLE,@PRO_METHOD,@PRO_CONTENT)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoProcedures model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Procedures] SET ");
            sql.Append("PRO_CONTESTS=@PRO_CONTESTS");
            sql.Append(",PRO_PHASES=@PRO_PHASES");
            sql.Append(",PRO_STARTTIME=@PRO_STARTTIME");
            sql.Append(",PRO_ENDTIME=@PRO_ENDTIME");
            sql.Append(",PRO_ADDRESS=@PRO_ADDRESS");
            sql.Append(",PRO_CONDUCTOR=@PRO_CONDUCTOR");
            sql.Append(",PRO_CNRTITLE=@PRO_CNRTITLE");
            sql.Append(",PRO_METHOD=@PRO_METHOD");
            sql.Append(",PRO_CONTENT=@PRO_CONTENT");
            sql.Append(" WHERE ");
            sql.Append("PRO_ID=@PRO_ID and PRO_IsShow='true'");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string pRO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Procedures");
            sql.Append(" WHERE ");
            sql.Append("PRO_ID=@PRO_ID and PRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pRO_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        public int DeleteProceduresByContest(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE Procedures SET PRO_IsShow='false' ");
            sql.Append(" WHERE ");
            sql.Append(" PRO_CONTESTS=@PRO_CONTESTS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoProcedures GetModel(string pRO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PRO_ID,PRO_CONTESTS,PRO_PHASES,PRO_STARTTIME,PRO_ENDTIME,PRO_ADDRESS,PRO_CONDUCTOR,PRO_CNRTITLE,PRO_METHOD,PRO_CONTENT,PHA_NAME,CON_NAME ");
            sql.Append(" FROM Procedures P INNER JOIN Phases H ON P.PRO_PHASES=H.PHA_ID INNER JOIN Contests C ON P.PRO_CONTESTS=C.CON_ID ");
            sql.Append(" WHERE ");
            sql.Append("PRO_ID=@PRO_ID and PRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pRO_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoProcedures> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoProcedures> GetProceduresByTime(DateTime date)
        {
            return GetProceduresByTime(0,date);
        }

         /// <summary>
        /// 通过时间和条数拿到相对应的数据
        /// </summary>
        /// <param name="topnumber"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public IList<BoProcedures> GetProceduresByTime(int topnumber, DateTime date)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT ");
            if (topnumber > 0)
            {
                sql.Append(" top ");
                sql.Append(topnumber);
            }
            sql.Append(" PRO_ID,PRO_CONTESTS,PRO_PHASES,PRO_STARTTIME,PRO_ENDTIME,PRO_ADDRESS,PRO_CONDUCTOR,PRO_CNRTITLE,PRO_METHOD,PRO_CONTENT,PHA_NAME,CON_NAME ");
            sql.Append(" FROM Procedures P INNER JOIN Phases H ON P.PRO_PHASES=H.PHA_ID INNER JOIN Contests C ON P.PRO_CONTESTS=C.CON_ID ");
            sql.Append(" WHERE PRO_STARTTIME>=@PRO_STARTTIME and PRO_IsShow='true' ORDER BY PRO_STARTTIME DESC");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_STARTTIME", SqlDbType.DateTime, 50);
            parameter[0].Value = date;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoProcedures> list = GetList(table);
            return list;
        }


        public IList<BoProcedures> GetProceduresByContest(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PRO_ID,PRO_CONTESTS,PRO_PHASES,PRO_STARTTIME,PRO_ENDTIME,PRO_ADDRESS,PRO_CONDUCTOR,PRO_CNRTITLE,PRO_METHOD,PRO_CONTENT,PHA_NAME,CON_NAME ");
            sql.Append(" FROM Procedures P INNER JOIN Phases H ON P.PRO_PHASES=H.PHA_ID INNER JOIN Contests C ON P.PRO_CONTESTS=C.CON_ID ");
            sql.Append(" WHERE PRO_CONTESTS=@PRO_CONTESTS and PRO_IsShow='true' ORDER BY PRO_STARTTIME DESC");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoProcedures> list = GetList(table);
            return list;
        }
        /// <summary>
        /// 通过过程id来获得相应的一条数据
        /// </summary>
        /// <param name="pro_id"></param>
        /// <returns></returns>
        public BoProcedures GetProceduresByProId(string pro_id)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PRO_ID,PRO_CONTESTS,PRO_PHASES,PRO_STARTTIME,PRO_ENDTIME,PRO_ADDRESS,PRO_CONDUCTOR,PRO_CNRTITLE,PRO_METHOD,PRO_CONTENT,PHA_NAME,CON_NAME ");
            sql.Append(" FROM Procedures P INNER JOIN Phases H ON P.PRO_PHASES=H.PHA_ID INNER JOIN Contests C ON P.PRO_CONTESTS=C.CON_ID ");
            sql.Append(" WHERE PRO_ID=@PRO_ID and PRO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pro_id;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoProcedures> list = GetList(table);
            return list.Count>0?list[0]:null;
        }

        private IList<BoProcedures> GetList(DataTable table)
        {
            List<BoProcedures> list = new List<BoProcedures>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoProcedures model = new BoProcedures();
                    model.PRO_ID = (string)table.Rows[i]["PRO_ID"];
                    model.PRO_CONTESTS = (string)table.Rows[i]["PRO_CONTESTS"];
                    model.PRO_PHASES = (string)table.Rows[i]["PRO_PHASES"];
                    model.PRO_STARTTIME = (DateTime)table.Rows[i]["PRO_STARTTIME"];
                    model.PRO_ENDTIME = (DateTime)table.Rows[i]["PRO_ENDTIME"];
                    model.PRO_ADDRESS = table.Rows[i]["PRO_ADDRESS"] == DBNull.Value ? "" : (string)table.Rows[i]["PRO_ADDRESS"];
                    model.PRO_CONDUCTOR = table.Rows[i]["PRO_CONDUCTOR"] == DBNull.Value ? "" : (string)table.Rows[i]["PRO_CONDUCTOR"];
                    model.PRO_CNRTITLE = table.Rows[i]["PRO_CNRTITLE"] == DBNull.Value ? "" : (string)table.Rows[i]["PRO_CNRTITLE"];
                    model.PRO_METHOD = table.Rows[i]["PRO_METHOD"] == DBNull.Value ? "" : (string)table.Rows[i]["PRO_METHOD"];
                    model.PHA_NAME = table.Rows[i]["PHA_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["PHA_NAME"];
                    model.CON_NAME = table.Rows[i]["CON_NAME"] == DBNull.Value ? "" : (string)table.Rows[i]["CON_NAME"];
                    
                    model.PRO_CONTENT = table.Rows[i]["PRO_CONTENT"] == DBNull.Value ? "" : (string)table.Rows[i]["PRO_CONTENT"];
                    /*if (table.Rows[i]["PRO_CONTENT"] == DBNull.Value)
                        model.PRO_CONTENT = "";
                    else
                        model.PRO_CONTENT = Encoding.Default.GetString((byte[])table.Rows[i]["PRO_CONTENT"]);*/
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoProcedures model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@PRO_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_CONTESTS",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_PHASES",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_STARTTIME",SqlDbType.DateTime,8),
                new SqlParameter("@PRO_ENDTIME",SqlDbType.DateTime,8),
                new SqlParameter("@PRO_ADDRESS",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_CONDUCTOR",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_CNRTITLE",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_METHOD",SqlDbType.NVarChar,50),
                new SqlParameter("@PRO_CONTENT",SqlDbType.Text,16),
            };
            parameters[0].Value = model.PRO_ID;
            parameters[1].Value = model.PRO_CONTESTS;
            parameters[2].Value = model.PRO_PHASES;
            parameters[3].Value = model.PRO_STARTTIME;
            parameters[4].Value = model.PRO_ENDTIME;
            parameters[5].Value = model.PRO_ADDRESS;
            parameters[6].Value = model.PRO_CONDUCTOR;
            parameters[7].Value = model.PRO_CNRTITLE;
            parameters[8].Value = model.PRO_METHOD;
            parameters[9].Value = model.PRO_CONTENT;
            return parameters;
        }
    }
}